{
 "cells": [
  {
   "cell_type": "markdown",
   "source": [
    "# DataWhale Mysql Task-03"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [
    "USE shop;\r\n",
    "USE view_students_info;"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "# 3.1视图\r\n",
    "\r\n",
    "## *视图与表的区别---“是否保存了实际的数据”"
   ],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "![](https://raw.githubusercontent.com/datawhalechina/wonderful-sql/main/img/ch03/ch03.01view.png)"
   ],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [
    "DROP VIEW IF EXISTS productsum;"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [
    "CREATE VIEW productsum (product_type, cnt_product)\r\n",
    "AS\r\n",
    "SELECT product_type, COUNT(*)\r\n",
    "  FROM product\r\n",
    " GROUP BY product_type ;"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [
    "CREATE TABLE shop_product\r\n",
    "(shop_id    CHAR(4)       NOT NULL,\r\n",
    " shop_name  VARCHAR(200)  NOT NULL,\r\n",
    " product_id CHAR(4)       NOT NULL,\r\n",
    " quantity   INTEGER       NOT NULL,\r\n",
    " PRIMARY KEY (shop_id, product_id));\r\n",
    "\r\n",
    "INSERT INTO shop_product (shop_id, shop_name, product_id, quantity) VALUES ('000A',\t'东京',\t\t'0001',\t30);\r\n",
    "INSERT INTO shop_product (shop_id, shop_name, product_id, quantity) VALUES ('000A',\t'东京',\t\t'0002',\t50);\r\n",
    "INSERT INTO shop_product (shop_id, shop_name, product_id, quantity) VALUES ('000A',\t'东京',\t\t'0003',\t15);\r\n",
    "INSERT INTO shop_product (shop_id, shop_name, product_id, quantity) VALUES ('000B',\t'名古屋',\t'0002',\t30);\r\n",
    "INSERT INTO shop_product (shop_id, shop_name, product_id, quantity) VALUES ('000B',\t'名古屋',\t'0003',\t120);\r\n",
    "INSERT INTO shop_product (shop_id, shop_name, product_id, quantity) VALUES ('000B',\t'名古屋',\t'0004',\t20);\r\n",
    "INSERT INTO shop_product (shop_id, shop_name, product_id, quantity) VALUES ('000B',\t'名古屋',\t'0006',\t10);\r\n",
    "INSERT INTO shop_product (shop_id, shop_name, product_id, quantity) VALUES ('000B',\t'名古屋',\t'0007',\t40);\r\n",
    "INSERT INTO shop_product (shop_id, shop_name, product_id, quantity) VALUES ('000C',\t'大阪',\t\t'0003',\t20);\r\n",
    "INSERT INTO shop_product (shop_id, shop_name, product_id, quantity) VALUES ('000C',\t'大阪',\t\t'0004',\t50);\r\n",
    "INSERT INTO shop_product (shop_id, shop_name, product_id, quantity) VALUES ('000C',\t'大阪',\t\t'0006',\t90);\r\n",
    "INSERT INTO shop_product (shop_id, shop_name, product_id, quantity) VALUES ('000C',\t'大阪',\t\t'0007',\t70);\r\n",
    "INSERT INTO shop_product (shop_id, shop_name, product_id, quantity) VALUES ('000D',\t'福冈',\t\t'0001',\t100);"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [
    "SELECT * from product;\r\n",
    "SELECT * from shop_product;"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [
    "DROP VIEW IF EXISTS view_shop_product;"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [
    "CREATE VIEW view_shop_product(product_type, sale_price, shop_name)\r\n",
    "AS\r\n",
    "SELECT product_type, sale_price, shop_name\r\n",
    "  FROM product,\r\n",
    "       shop_product\r\n",
    " WHERE product.product_id = shop_product.product_id;"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [
    "SELECT sale_price, shop_name\r\n",
    "  FROM view_shop_product\r\n",
    " WHERE product_type = '衣服';"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [
    "ALTER VIEW productSum\r\n",
    "    AS\r\n",
    "        SELECT product_type, sale_price\r\n",
    "        FROM Product\r\n",
    "        WHERE regist_date > '2009-09-11';"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "## * 聚合函数 SUM()、MIN()、MAX()、COUNT() 等。\r\n",
    "## * DISTINCT 关键字。\r\n",
    "## * GROUP BY 子句。\r\n",
    "## * HAVING 子句。\r\n",
    "## * UNION 或 UNION ALL 运算符。\r\n",
    "## * FROM 子句中包含多个表。\r\n",
    "## 视图归根结底还是从表派生出来的，因此，如果原表可以更新，那么 视图中的数据也可以更新。反之亦然，如果视图发生了改变，而原表没有进行相应更新的话，就无法保证数据的一致性了。"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [
    "UPDATE productsum\r\n",
    "   SET sale_price = '100'\r\n",
    " WHERE product_type = '办公用品';"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "# 3.2 子查询\r\n",
    "\r\n",
    "## * 视图生命周期类似于栈内数据\r\n",
    "## * 子查询生命周期则类似于匿名对象"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [
    "SELECT product_type, cnt_product\r\n",
    "FROM (SELECT *\r\n",
    "        FROM (SELECT product_type, \r\n",
    "                      COUNT(*) AS cnt_product\r\n",
    "                FROM product \r\n",
    "               GROUP BY product_type) AS productsum\r\n",
    "       WHERE cnt_product = 4) AS productsum2;"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "## 3.2.5 标量子查询有什么用\r\n",
    "## * 仅返回一个格子"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [
    "SELECT AVG(sale_price) FROM product;\r\n",
    "SELECT product_id, product_name, sale_price\r\n",
    "  FROM product\r\n",
    " WHERE sale_price > (SELECT AVG(sale_price) FROM product);"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [
    "SELECT product_id,\r\n",
    "       product_name,\r\n",
    "       sale_price,\r\n",
    "       (SELECT AVG(sale_price)\r\n",
    "          FROM product) AS avg_price\r\n",
    "  FROM product;"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "# 3.2.6 关联子查询\r\n",
    "## * 查询与子查询之间存在着联系"
   ],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "## 选取出各商品种类中高于该商品种类的平均销售单价的商品"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [
    "SELECT product_type, product_name, sale_price\r\n",
    "  FROM product AS p1\r\n",
    " WHERE sale_price > (SELECT AVG(sale_price)\r\n",
    "                       FROM product AS p2\r\n",
    "                      WHERE p1.product_type = p2.product_type\r\n",
    "   GROUP BY product_type);----都分组了\r\n",
    "---为啥多了个500块的打孔器"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "# 关联子查询执行逻辑如下：\r\n",
    "\r\n",
    "## * （1）先从主查询的Product表中product _type列取出第一个值，进入子查询中，得到子查询结果，然后返回父查询，判断父查询的where子句条件，则返回整个语句的第1条结果。\r\n",
    "\r\n",
    "## * （2）重复上述操作，直到所有主查询中的Product表中product _type列记录取完为止。得出整个语句的结果集，就是最后的答案。"
   ],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "# 常用函数"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [
    "-- DDL ：创建表\r\n",
    "USE shop;\r\n",
    "DROP TABLE IF EXISTS samplemath;\r\n",
    "CREATE TABLE samplemath\r\n",
    "(m float(10,3),\r\n",
    "n INT,\r\n",
    "p INT);\r\n",
    "\r\n",
    "-- DML ：插入数据\r\n",
    "START TRANSACTION; -- 开始事务\r\n",
    "INSERT INTO samplemath(m, n, p) VALUES (500, 0, NULL);\r\n",
    "INSERT INTO samplemath(m, n, p) VALUES (-180, 0, NULL);\r\n",
    "INSERT INTO samplemath(m, n, p) VALUES (NULL, NULL, NULL);\r\n",
    "INSERT INTO samplemath(m, n, p) VALUES (NULL, 7, 3);\r\n",
    "INSERT INTO samplemath(m, n, p) VALUES (NULL, 5, 2);\r\n",
    "INSERT INTO samplemath(m, n, p) VALUES (NULL, 4, NULL);\r\n",
    "INSERT INTO samplemath(m, n, p) VALUES (8, NULL, 3);\r\n",
    "INSERT INTO samplemath(m, n, p) VALUES (2.27, 1, NULL);\r\n",
    "INSERT INTO samplemath(m, n, p) VALUES (5.555,2, NULL);\r\n",
    "INSERT INTO samplemath(m, n, p) VALUES (NULL, 1, NULL);\r\n",
    "INSERT INTO samplemath(m, n, p) VALUES (8.76, NULL, NULL);\r\n",
    "COMMIT; -- 提交事务\r\n",
    "-- 查询表内容\r\n",
    "SELECT * FROM samplemath;"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [
    "SELECT m,\r\n",
    "ABS(m) AS abs_col ,\r\n",
    "n, \r\n",
    "p,\r\n",
    "MOD(n, p) AS mod_col,\r\n",
    "ROUND(m,1) AS round_colS\r\n",
    "FROM samplemath;"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [
    "-- DDL ：创建表\r\n",
    "USE  shop;\r\n",
    "DROP TABLE IF EXISTS samplestr;\r\n",
    "CREATE TABLE samplestr\r\n",
    "(str1 VARCHAR (40),\r\n",
    "str2 VARCHAR (40),\r\n",
    "str3 VARCHAR (40)\r\n",
    ");\r\n",
    "-- DML：插入数据\r\n",
    "START TRANSACTION;\r\n",
    "INSERT INTO samplestr (str1, str2, str3) VALUES ('opx',\t'rt', NULL);\r\n",
    "INSERT INTO samplestr (str1, str2, str3) VALUES ('abc', 'def', NULL);\r\n",
    "INSERT INTO samplestr (str1, str2, str3) VALUES ('太阳',\t'月亮', '火星');\r\n",
    "INSERT INTO samplestr (str1, str2, str3) VALUES ('aaa',\tNULL, NULL);\r\n",
    "INSERT INTO samplestr (str1, str2, str3) VALUES (NULL, 'xyz', NULL);\r\n",
    "INSERT INTO samplestr (str1, str2, str3) VALUES ('@!#$%', NULL, NULL);\r\n",
    "INSERT INTO samplestr (str1, str2, str3) VALUES ('ABC', NULL, NULL);\r\n",
    "INSERT INTO samplestr (str1, str2, str3) VALUES ('aBC', NULL, NULL);\r\n",
    "INSERT INTO samplestr (str1, str2, str3) VALUES ('abc哈哈',  'abc', 'ABC');\r\n",
    "INSERT INTO samplestr (str1, str2, str3) VALUES ('abcdefabc', 'abc', 'ABC');\r\n",
    "INSERT INTO samplestr (str1, str2, str3) VALUES ('micmic', 'i', 'I');\r\n",
    "COMMIT;\r\n",
    "-- 确认表中的内容\r\n",
    "SELECT * FROM samplestr;"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [
    "SELECT SUBSTRING_INDEX('www.mysql.com', '.', -1);"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [
    "SELECT SUBSTRING('www.mysql.com' FROM 4 FOR 4) ;\r\n",
    "---from index for len;"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [
    "SELECT SUBSTRING_INDEX(SUBSTRING_INDEX('www.mysql.com', '.', -2), '.', 1);\r\n",
    "SELECT SUBSTRING_INDEX(SUBSTRING_INDEX('www.mysql.com', '.', 2), '.', -1);"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [
    "SELECT REPEAT('mother fucker ',3) AS str;"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [
    "SELECT CURRENT_DATE;\r\n",
    "\r\n",
    "SELECT CURRENT_TIME;\r\n",
    "\r\n",
    "SELECT CURRENT_TIMESTAMP;\r\n",
    "\r\n",
    "SELECT CURRENT_TIMESTAMP as now,\r\n",
    "EXTRACT(YEAR   FROM CURRENT_TIMESTAMP) AS year,\r\n",
    "EXTRACT(MONTH  FROM CURRENT_TIMESTAMP) AS month,\r\n",
    "EXTRACT(DAY    FROM CURRENT_TIMESTAMP) AS day,\r\n",
    "EXTRACT(HOUR   FROM CURRENT_TIMESTAMP) AS hour,\r\n",
    "EXTRACT(MINUTE FROM CURRENT_TIMESTAMP) AS MINute,\r\n",
    "EXTRACT(SECOND FROM CURRENT_TIMESTAMP) AS second;\r\n"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [
    "SELECT CAST('0001' AS SIGNED INTEGER) AS int_col;"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [
    "SELECT CAST('2009-12-14' AS DATE) AS date_col;"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [
    "SELECT COALESCE(NULL, 11) AS col_1,\r\n",
    "COALESCE(NULL, 'hello world', NULL) AS col_2,\r\n",
    "COALESCE(NULL, NULL, '2020-11-01') AS col_3;"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "# 3.4.2 LIKE谓词 -- 用于字符串的部分一致查询"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [
    "-- coalesce\r\n",
    "-- DDL ：创建表\r\n",
    "CREATE TABLE samplelike\r\n",
    "( strcol VARCHAR(6) NOT NULL,\r\n",
    "PRIMARY KEY (strcol)\r\n",
    "samplelike);\r\n",
    "-- DML ：插入数据\r\n",
    "START TRANSACTION; -- 开始事务\r\n",
    "INSERT INTO samplelike (strcol) VALUES ('abcddd');\r\n",
    "INSERT INTO samplelike (strcol) VALUES ('dddabc');\r\n",
    "INSERT INTO samplelike (strcol) VALUES ('abdddc');\r\n",
    "INSERT INTO samplelike (strcol) VALUES ('abcdd');\r\n",
    "INSERT INTO samplelike (strcol) VALUES ('ddabc');\r\n",
    "INSERT INTO samplelike (strcol) VALUES ('abddc');\r\n",
    "COMMIT; -- 提交事务\r\n",
    "SELECT * FROM samplelike;"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [
    "SELECT *\r\n",
    "FROM samplelike\r\n",
    "WHERE strcol LIKE 'a%';\r\n",
    "\r\n",
    "---LIKE 模式匹配\r\n",
    "---其中的%是代表“零个或多个任意字符串”的特殊符号，本例中代表“以ddd开头的所有字符串”。"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [
    "SELECT *\r\n",
    "FROM samplelike\r\n",
    "WHERE strcol LIKE '%ddd%';"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [
    "SELECT *\r\n",
    "FROM samplelike\r\n",
    "WHERE strcol LIKE 'abc__';\r\n",
    "--_仅代表一个字符"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "## BETWEEN 的特点就是结果是闭区间"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [
    "-- 选取销售单价为100～ 1000元的商品\r\n",
    "SELECT product_name, sale_price\r\n",
    "FROM product\r\n",
    "WHERE sale_price BETWEEN 100 AND 1000;\r\n",
    "\r\n",
    "SELECT product_name, sale_price\r\n",
    "FROM product\r\n",
    "WHERE sale_price > 100\r\n",
    "AND sale_price < 1000;"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [
    "SELECT product_name, purchase_price\r\n",
    "FROM product\r\n",
    "WHERE purchase_price IS NULL;\r\n",
    "\r\n",
    "SELECT product_name, purchase_price\r\n",
    "FROM product\r\n",
    "WHERE purchase_price IS NOT NULL;\r\n",
    "\r\n",
    "-- 通过OR指定多个进货单价进行查询\r\n",
    "SELECT product_name, purchase_price\r\n",
    "FROM product\r\n",
    "WHERE purchase_price = 320\r\n",
    "OR purchase_price = 500\r\n",
    "OR purchase_price = 5000;\r\n",
    "\r\n",
    "SELECT product_name, purchase_price\r\n",
    "FROM product\r\n",
    "WHERE purchase_price IN (320, 500, 5000);\r\n",
    "\r\n",
    "SELECT product_name, purchase_price\r\n",
    "FROM product\r\n",
    "WHERE purchase_price NOT IN (320, 500, 5000);"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "# 3.4.6 使用子查询作为IN谓词的参数"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [
    "-- DDL ：创建表\r\n",
    "DROP TABLE IF EXISTS shopproduct;\r\n",
    "CREATE TABLE shopproduct\r\n",
    "(  shop_id CHAR(4)     NOT NULL,\r\n",
    " shop_name VARCHAR(200) NOT NULL,\r\n",
    "product_id CHAR(4)      NOT NULL,\r\n",
    "  quantity INTEGER      NOT NULL,\r\n",
    "PRIMARY KEY (shop_id, product_id) -- 指定主键\r\n",
    ");\r\n",
    "-- DML ：插入数据\r\n",
    "START TRANSACTION; -- 开始事务\r\n",
    "INSERT INTO shopproduct (shop_id, shop_name, product_id, quantity) VALUES ('000A', '东京', '0001', 30);\r\n",
    "INSERT INTO shopproduct (shop_id, shop_name, product_id, quantity) VALUES ('000A', '东京', '0002', 50);\r\n",
    "INSERT INTO shopproduct (shop_id, shop_name, product_id, quantity) VALUES ('000A', '东京', '0003', 15);\r\n",
    "INSERT INTO shopproduct (shop_id, shop_name, product_id, quantity) VALUES ('000B', '名古屋', '0002', 30);\r\n",
    "INSERT INTO shopproduct (shop_id, shop_name, product_id, quantity) VALUES ('000B', '名古屋', '0003', 120);\r\n",
    "INSERT INTO shopproduct (shop_id, shop_name, product_id, quantity) VALUES ('000B', '名古屋', '0004', 20);\r\n",
    "INSERT INTO shopproduct (shop_id, shop_name, product_id, quantity) VALUES ('000B', '名古屋', '0006', 10);\r\n",
    "INSERT INTO shopproduct (shop_id, shop_name, product_id, quantity) VALUES ('000B', '名古屋', '0007', 40);\r\n",
    "INSERT INTO shopproduct (shop_id, shop_name, product_id, quantity) VALUES ('000C', '大阪', '0003', 20);\r\n",
    "INSERT INTO shopproduct (shop_id, shop_name, product_id, quantity) VALUES ('000C', '大阪', '0004', 50);\r\n",
    "INSERT INTO shopproduct (shop_id, shop_name, product_id, quantity) VALUES ('000C', '大阪', '0006', 90);\r\n",
    "INSERT INTO shopproduct (shop_id, shop_name, product_id, quantity) VALUES ('000C', '大阪', '0007', 70);\r\n",
    "INSERT INTO shopproduct (shop_id, shop_name, product_id, quantity) VALUES ('000D', '福冈', '0001', 100);\r\n",
    "COMMIT; -- 提交事务\r\n",
    "SELECT * FROM shopproduct;"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [
    "SELECT product_id\r\n",
    "FROM shopproduct\r\n",
    "WHERE shop_id = '000C';"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [
    "SELECT product_name, sale_price\r\n",
    "FROM product\r\n",
    "WHERE product_id IN (SELECT product_id\r\n",
    "                    FROM shopproduct\r\n",
    "                    WHERE shop_id = '000C');\r\n",
    "\r\n",
    "-- 子查询展开后的结果\r\n",
    "SELECT product_name, sale_price\r\n",
    "FROM product\r\n",
    "WHERE product_id IN ('0003', '0004', '0006', '0007');"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [
    "-- NOT IN 使用子查询作为参数，取出未在大阪门店销售的商品的销售单价\r\n",
    "SELECT product_name, sale_price\r\n",
    "  FROM product\r\n",
    " WHERE product_id NOT IN (SELECT product_id\r\n",
    "                            FROM shopproduct\r\n",
    "                           WHERE shop_id = '000A');"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [
    "SELECT product_name, sale_price\r\n",
    "  FROM product AS p\r\n",
    " WHERE EXISTS (SELECT *\r\n",
    "                 FROM shopproduct AS sp\r\n",
    "                WHERE sp.shop_id = '000C'\r\n",
    "                  AND sp.product_id = p.product_id);\r\n",
    "\r\n",
    "----即替换了IN，注意EXISTS左边没有参数"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "## * 可以把在 EXIST 的子查询中书写 SELECT * 当作 SQL 的一种习惯。"
   ],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "# 3.5 CASE 表达式"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [
    "-- 对按照商品种类计算出的销售单价合计值进行行列转换\r\n",
    "SELECT SUM(CASE WHEN product_type = '衣服' THEN sale_price ELSE 0 END) AS sum_price_clothes,\r\n",
    "       SUM(CASE WHEN product_type = '厨房用具' THEN sale_price ELSE 0 END) AS sum_price_kitchen,\r\n",
    "       SUM(CASE WHEN product_type = '办公用品' THEN sale_price ELSE 0 END) AS sum_price_office\r\n",
    "  FROM product;"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [
    "-- CASE WHEN 实现数字列 score 行转列\r\n",
    "DROP TABLE IF EXISTS score;\r\n",
    "CREATE TABLE score\r\n",
    "( name VARCHAR(4)     NOT NULL,\r\n",
    "  subject VARCHAR(200) NOT NULL,\r\n",
    "  score int(3)      NOT NULL\r\n",
    "-- PRIMARY KEY (name) -- 指定主键\r\n",
    ");\r\n",
    "INSERT INTO score (name, subject, score) VALUES ('张三', '语文', 93);\r\n",
    "INSERT INTO score (name, subject, score) VALUES ('张三', '数学', 88);\r\n",
    "INSERT INTO score (name, subject, score) VALUES ('张三', '外语', 91);\r\n",
    "INSERT INTO score (name, subject, score) VALUES ('李四', '语文', 87);\r\n",
    "INSERT INTO score (name, subject, score) VALUES ('李四', '数学', 90);\r\n",
    "INSERT INTO score (name, subject, score) VALUES ('李四', '外语', 77);\r\n",
    "SELECT name,\r\n",
    "       SUM(CASE WHEN subject = '语文' THEN score ELSE null END) as chinese,\r\n",
    "       SUM(CASE WHEN subject = '数学' THEN score ELSE null END) as math,\r\n",
    "       SUM(CASE WHEN subject = '外语' THEN score ELSE null END) as english\r\n",
    "  FROM score\r\n",
    " GROUP BY name;\r\n",
    "\r\n",
    " -- CASE WHEN 实现文本列 subject 行转列\r\n",
    "SELECT name,\r\n",
    "       MAX(CASE WHEN subject = '语文' THEN subject ELSE null END) as chinese,\r\n",
    "       MAX(CASE WHEN subject = '数学' THEN subject ELSE null END) as math,\r\n",
    "       MIN(CASE WHEN subject = '外语' THEN subject ELSE null END) as english\r\n",
    "  FROM score\r\n",
    " GROUP BY name;"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "## * 当待转换列为数字时，可以使用SUM AVG MAX MIN等聚合函数；\r\n",
    "\r\n",
    "## * 当待转换列为文本时，可以使用MAX MIN等聚合函数"
   ],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "# 练习题-第一部分"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [
    "SELECT * FROM product;"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [
    "CREATE VIEW ViewPractice5_1 (product_name , sale_price ,regist_date)\r\n",
    "AS\r\n",
    "SELECT product_name , sale_price ,regist_date\r\n",
    "FROM product\r\n",
    "WHERE sale_price >= 1000 and regist_date = '2009-09-20'\r\n",
    "--  GROUP BY product_type ;"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [
    "INSERT INTO ViewPractice5_1 VALUES (' 刀子 ', 300, '2009-11-02');\r\n",
    "--------只能修改透过窗口能看到的内容。"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [
    "SELECT product_id,\r\n",
    "       product_name,\r\n",
    "       product_type,\r\n",
    "       sale_price,\r\n",
    "       (SELECT AVG(sale_price)\r\n",
    "          FROM product) AS sale_price_all\r\n",
    "  FROM product;"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [
    "CREATE VIEW AvgPriceByType(product_id,product_type, product_name, sale_price,avg_sale_price)\r\n",
    "AS\r\n",
    "SELECT  product_id,\r\n",
    "        product_type, \r\n",
    "        product_name, \r\n",
    "        sale_price,\r\n",
    "        (SELECT AVG(sale_price)\r\n",
    "        FROM product AS p2\r\n",
    "        WHERE p1.product_type = p2.product_type\r\n",
    "        GROUP BY product_type) AS avg_sale_price\r\n",
    "FROM product AS p1;"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "# 练习题-第二部分"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [
    "--不一定，SUM会忽略，COUNT也是"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [
    "SELECT product_name, purchase_price\r\n",
    "  FROM product\r\n",
    " WHERE purchase_price NOT IN (500, 2800, 5000);\r\n",
    " SELECT product_name, purchase_price\r\n",
    "  FROM product\r\n",
    " WHERE purchase_price IN (500, 2800, 5000);"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [
    "SELECT product_name, purchase_price\r\n",
    "  FROM product\r\n",
    " WHERE purchase_price IN ( NULL);\r\n",
    "---NULL不作为IN考虑\r\n",
    "\r\n",
    " SELECT product_name, purchase_price\r\n",
    "  FROM product\r\n",
    " WHERE purchase_price NOT IN (500, 2800, 5000, NULL);"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [
    "-- SELECT COUNT(*)\r\n",
    "-- FROM "
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [
    "SELECT COUNT(CASE WHEN sale_price <= 1000 THEN 1 ELSE NULL END) AS low_price ,\r\n",
    "       COUNT(CASE WHEN sale_price BETWEEN 1001 AND 3000 THEN 1 ELSE NULL END) AS middle_price ,\r\n",
    "       COUNT(CASE WHEN sale_price >= 3001  THEN 1 ELSE NULL END) AS high_price \r\n",
    "  FROM product;"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [
    "-- 对按照商品种类计算出的销售单价合计值进行行列转换\r\n",
    "-- SELECT SUM(CASE WHEN product_type = '衣服' THEN sale_price ELSE 0 END) AS sum_price_clothes,\r\n",
    "--        SUM(CASE WHEN product_type = '厨房用具' THEN sale_price ELSE 0 END) AS sum_price_kitchen,\r\n",
    "--        SUM(CASE WHEN product_type = '办公用品' THEN sale_price ELSE 0 END) AS sum_price_office\r\n",
    "--   FROM product;"
   ],
   "outputs": [],
   "metadata": {}
  }
 ],
 "metadata": {
  "orig_nbformat": 4,
  "language_info": {
   "name": "python"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}